package yiguan;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.ServletException;
import java.util.Properties;
import java.util.Hashtable;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.json.JSONObject;
import org.json.JSONArray;
import org.json.JSONException;
import org.apache.log4j.PropertyConfigurator;
import org.apache.log4j.Logger;

public class BaseServlet extends HttpServlet {
	private static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
	private static final String DBURL = "jdbc:derby:CanDB";
	private Logger logger = Logger.getLogger(this.getClass());
	private Properties bundleMsg = new Properties();
	private static Properties allStreet_ = new Properties();
	private static Properties allCommunity_ = new Properties();


	public void service(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		response.setCharacterEncoding("UTF-8");
    	String action = request.getParameter("action");
    	if (isEmpty(action)) {
    		logger.error("action is null");
    		response.getWriter().print("error url parameters");
    		response.getWriter().close();
    		return;
    	}

    	debugParameters(request);
    	response.setContentType("text/json;charset=UTF-8");
    	int actionKey;
    	try {
    		actionKey = Integer.parseInt(action);
    	} catch (NumberFormatException e) {
    		logger.error("action = " + action);
    		responseMessage(-1, "Invalid URL: action=" + action, response);
    		return;
    	}

    	switch (actionKey) {
    		case 1 : streetManage(request, response); break;
    		case 2 : communityManage(request, response); break;
    		case 3 : getCommunitiesByStreet(request, response); break;
    		case 11: buildStreetSelect(request, response); break;
    		case 12: getAllStreetCommunity(request, response); break;
    		case 13: getAllDistrictStreet(request, response); break;
    		//case 14: loadAllStreetCommunity(request, response); break;
    		default: responseMessage(1, "invalid action : " + action, response); break;
    	}
	}

	private void streetManage(HttpServletRequest request, HttpServletResponse response) {
		String oper = request.getParameter("oper");

		if (oper == null)
			streetAll(request, response);
		else if (oper.equals("add"))
			streetAdd(request, response);
		else if (oper.equals("edit"))
			streetEdt(request, response);
		else if (oper.equals("del"))
			streetDel(request, response);
		else if (oper.equals("excel"))
			streetExcel(request, response);
		else
			responseMessage(1, "Invalid oper paremeter : " + oper, response);
	}

	private void streetExcel(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		java.io.PrintWriter out;
		String sqlWhere = "";
		String district = request.getParameter("district");
		if ((district != null) && (!district.equals("0")))
			sqlWhere = " where district='" + district + "'";

		response.reset();
		response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".csv");
		response.setContentType("application/vnd.ms-excel; charset=GBK");
		try {
			Properties bundle = new Properties();
			bundle.load(BaseServlet.class.getResourceAsStream("Street.properties"));
			out = response.getWriter();
			for (int i = 1; i <= 6; i++) {
				sb.append(bundle.getProperty("col" + i));
				if (i < 6)
					sb.append(",");
			}
			out.println(sb.toString());

			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from street" + sqlWhere + " order by sid");
			while (rs.next()) {
				sb = new StringBuffer();
				sb.append(rs.getString("sid"));
				sb.append(",");
				sb.append(rs.getString("sname").trim());
				sb.append(",");
				sb.append(bundle.getProperty("district" + rs.getString("district")));
				sb.append(",");
				sb.append(rs.getString("address").trim());
				sb.append(",");
				sb.append(rs.getString("tel").trim());
				sb.append(",");
				sb.append(rs.getString("postcode"));
				out.println(sb.toString());
			}
			out.flush();
			out.close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(IOException e2) {
  			logger.error(e2.getMessage(), e2);
  		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void streetAll(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		JSONArray matrix = new JSONArray();
		JSONObject grid = new JSONObject();
		JSONObject userData = new JSONObject();
		String sqlWhere = "";
		String district = request.getParameter("district");
		if ((district != null) && (!district.equals("0")))
			sqlWhere = " where district='" + district + "'";

		String page = request.getParameter("page");
		int total, offset;

		try {
			int n = Integer.parseInt(page);
			offset = n * 20 - 20;
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) from street" + sqlWhere);
			rs.next();
			total = rs.getInt(1);
			rs = stmt.executeQuery("select * from street" + sqlWhere + " order by sid offset " + offset + " rows fetch next 20 rows only");
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString("sid"));
				String[] ss = new String[6];
				ss[0] = rs.getString("sid");
				ss[1] = rs.getString("sname").trim();
				ss[2] = rs.getString("district");
				ss[3] = rs.getString("address").trim();
				ss[4] = rs.getString("tel").trim();
				ss[5] = rs.getString("postcode");
				so.put("cell", ss);
				matrix.put(so);
			}

			grid.put("page", page);
			grid.put("total",(total / 20) + 1);
			grid.put("records", total);
			grid.put("rows", matrix);
			userData.put("udistrict", (String)request.getSession().getAttribute("udistrict"));
			userData.put("usid", (String)request.getSession().getAttribute("usid"));
			grid.put("userdata", userData);
			response.getWriter().print(grid);
	  		response.getWriter().close();
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void streetAdd(HttpServletRequest request, HttpServletResponse response) {
		String sid = request.getParameter("sid");
		String sname = request.getParameter("sname");
		String district = request.getParameter("district");
		String address = request.getParameter("address");
		String tel = request.getParameter("tel");
		String postcode = request.getParameter("postcode");
		String uid = (String)request.getSession().getAttribute("uid");

		if ((uid != null) && (!uid.equals("130700"))) {
			String dis = (String) request.getSession().getAttribute("udistrict");
			if (!district.equals(dis)) {
				responseMessage(999, "not same district", response);
				return;
			}
		}

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("insert into street(sid,sname,district,address,tel,postcode) values(?,?,?,?,?,?)");
			ps.setString(1, sid);
			ps.setString(2, sname);
			ps.setString(3, district);
			ps.setString(4, address);
			ps.setString(5, tel);
			ps.setString(6, postcode);
			ps.executeUpdate();
			responseMessage(0, "succeed", response);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void streetEdt(HttpServletRequest request, HttpServletResponse response) {
		String sid = request.getParameter("sid");
		String sname = request.getParameter("sname");
		String district = request.getParameter("district");
		String address = request.getParameter("address");
		String tel = request.getParameter("tel");
		String postcode = request.getParameter("postcode");
		String uid = (String)request.getSession().getAttribute("uid");

		if ((uid != null) && (!uid.equals("130700"))) {
			String dis = (String) request.getSession().getAttribute("udistrict");
			if (!district.equals(dis)) {
				responseMessage(999, "not same district", response);
				return;
			}
		}

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("update street set sname=?,district=?,address=?,tel=?,postcode=? where sid=?");
			ps.setString(1, sname);
			ps.setString(2, district);
			ps.setString(3, address);
			ps.setString(4, tel);
			ps.setString(5, postcode);
			ps.setString(6, sid);
			ps.executeUpdate();
			responseMessage(0, "succeed", response);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void streetDel(HttpServletRequest request, HttpServletResponse response) {
		String sid = request.getParameter("id");
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("delete from street where sid=?");
			ps.setString(1, sid);
			int r = ps.executeUpdate();
			responseMessage(0, "succeed", response);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void communityManage(HttpServletRequest request, HttpServletResponse response) {
		String oper = request.getParameter("oper");

		if (oper == null)
			communityAll(request, response);
		else if (oper.equals("add"))
			communityAdd(request, response);
		else if (oper.equals("edit"))
			communityEdt(request, response);
		else if (oper.equals("del"))
			communityDel(request, response);
		else if (oper.equals("excel"))
			communityExcel(request, response);
		else
			responseMessage(1, "Invalid oper paremeter : " + oper, response);
	}

	private void communityAll(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		JSONArray matrix = new JSONArray();
		JSONObject grid = new JSONObject();
		JSONObject userData = new JSONObject();
		String district = request.getParameter("district");
		String sid = request.getParameter("sid");
		String page = request.getParameter("page");
		String sqlWhere = "", sqlWhere1 = "";
		int total, offset;

		if ((district != null) && (!district.equals("0"))) {
			sqlWhere  += " and district='" + district + "'";
			sqlWhere1 += " and c.district='" + district + "'";
		}
		if ((sid != null) && (!sid.equals("0"))) {
			sqlWhere  += " and sid='" + sid + "'";
			sqlWhere1 += " and c.sid='" + sid + "'";
		}

		try {
			int n = Integer.parseInt(page);
			offset = n * 20 - 20;
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) from community where 1=1" + sqlWhere);
			rs.next();
			total = rs.getInt(1);
			rs = stmt.executeQuery("select c.cid cid,c.cname cname,c.district district,c.sid sid," +
				"s.sname sname,c.tel tel,c.address address from community c,street s " +
				"where c.sid=s.sid" + sqlWhere1 + " order by c.cid offset " + offset +
				" rows fetch next 20 rows only");
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString("cid"));
				String[] ss = new String[7];
				ss[0] = rs.getString("cid");
				ss[1] = rs.getString("cname").trim();
				ss[2] = rs.getString("district");
				ss[3] = rs.getString("sid");
				ss[4] = rs.getString("sname").trim();
				ss[5] = rs.getString("tel").trim();
				ss[6] = rs.getString("address").trim();
				so.put("cell", ss);
				matrix.put(so);
			}

			grid.put("page", page);
			grid.put("total",(total / 20) + 1);
			grid.put("records", total);
			grid.put("rows", matrix);
			userData.put("udistrict", (String)request.getSession().getAttribute("udistrict"));
			userData.put("usid", (String)request.getSession().getAttribute("usid"));
			grid.put("userdata", userData);
			response.getWriter().print(grid);
	  		response.getWriter().close();
		} catch(Exception e) {
			logger.error(e.getMessage(), e);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void communityExcel(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		java.io.PrintWriter out;
		StringBuffer sb = new StringBuffer();
		String sqlWhere = " where 1=1";
		String district = request.getParameter("district");
		String sid = request.getParameter("sid");

		if ((district != null) && (!district.equals("0")))
			sqlWhere  += " and district='" + district + "'";
		if ((sid != null) && (!sid.equals("0")))
			sqlWhere  += " and sid='" + sid + "'";

		response.reset();
		response.setHeader("Content-Disposition", "attachment; filename=" + System.currentTimeMillis() + ".csv");
		response.setContentType("application/vnd.ms-excel; charset=GBK");
		try {
			Properties bundle = new Properties();
			bundle.load(BaseServlet.class.getResourceAsStream("Street.properties"));
			out = response.getWriter();
			for (int i = 1; i <= 6; i++) {
				sb.append(bundle.getProperty("column" + i));
				if (i < 6)
					sb.append(",");
			}
			out.println(sb.toString());

			Properties streets = DBUtil.getAllStreet();
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from community" + sqlWhere + " order by cid");
			while (rs.next()) {
				sb = new StringBuffer();
				sb.append(rs.getString("cid"));
				sb.append(",");
				sb.append(rs.getString("cname").trim());
				sb.append(",");
				sb.append(bundle.getProperty("district" + rs.getString("district")));
				sb.append(",");
				sid = rs.getString("sid");
				sb.append(sid == null? "":streets.getProperty(sid));
				sb.append(",");
				sb.append(rs.getString("tel").trim());
				sb.append(",");
				sb.append(rs.getString("address").trim());
				out.println(sb.toString());
			}
			out.flush();
			out.close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
		} catch(IOException e2) {
  			logger.error(e2.getMessage(), e2);
  		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void communityAdd(HttpServletRequest request, HttpServletResponse response) {
		String cid = request.getParameter("cid");
		String cname = request.getParameter("cname");
		String district = (String) request.getSession().getAttribute("udistrict");
		String sid = request.getParameter("sid");
		String tel = request.getParameter("tel");
		String address = request.getParameter("address");
		String uid = (String)request.getSession().getAttribute("uid");

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("insert into community(cid,cname,district,sid,tel,address) values(?,?,?,?,?,?)");
			ps.setString(1, cid);
			ps.setString(2, cname);
			ps.setString(3, district);
			ps.setString(4, sid);
			ps.setString(5, tel);
			ps.setString(6, address);
			ps.executeUpdate();
			responseMessage(0, "succeed", response);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void communityEdt(HttpServletRequest request, HttpServletResponse response) {
		String cid = request.getParameter("cid");
		String cname = request.getParameter("cname");
		String district = (String)request.getSession().getAttribute("udistrict");
		String sid = request.getParameter("sid");
		String tel = request.getParameter("tel");
		String address = request.getParameter("address");
		String uid = (String)request.getSession().getAttribute("uid");

//		if ((uid != null) && (!uid.equals("0000"))) {
//			String dis = (String) request.getSession().getAttribute("udistrict");
//			if (!dis.equals(district)) {
//				responseMessage(999, "not same district", response);
//				return;
//			}
//		}

		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("update community set cname=?,district=?,sid=?,tel=?,address=? where cid=?");
			ps.setString(1, cname);
			ps.setString(2, district);
			ps.setString(3, sid);
			ps.setString(4, tel);
			ps.setString(5, address);
			ps.setString(6, cid);
			ps.executeUpdate();
			responseMessage(0, "succeed", response);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void communityDel(HttpServletRequest request, HttpServletResponse response) {
		String cid = request.getParameter("id");
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("delete from community where cid=?");
			ps.setString(1, cid);
			int r = ps.executeUpdate();
			responseMessage(0, "succeed", response);
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} finally {
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void getAllDistrictStreet(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		JSONObject jsonStreet = null;
		JSONArray sa = new JSONArray();
		JSONObject retval = new JSONObject();
		String district = (String)request.getSession().getAttribute("udistrict");

		try {
			conn = openConnection();
			stmt = conn.createStatement();
			if (district.equals("0"))
				rs = stmt.executeQuery("select sid,sname from street");
			else
				rs = stmt.executeQuery("select sid,sname from street where district='" + district + "'");
			while (rs.next()) {
				String sid      = rs.getString(1);
				String sname    = rs.getString(2).trim();
				jsonStreet = new JSONObject();
				jsonStreet.put("sid", sid);
				jsonStreet.put("sname", sname);
				sa.put(jsonStreet);
			}
			retval.put("udistrict", district);
			retval.put("streets", sa);
			response.getWriter().print(retval);
	  		response.getWriter().close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} catch(IOException e1) {
			logger.error(e1.getMessage(), e1);
		} catch(JSONException e2) {
			logger.error(e2.getMessage(), e2);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void getAllStreetCommunity(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs1 = null;
		JSONObject jsonStreet = null;
		JSONArray jsonAllStreet = new JSONArray();
		JSONArray jsonCommunitys = null;
		JSONObject j = null;

		try {
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select sid,sname,district from street order by sid");
			ps = conn.prepareStatement("select cid,cname from community where sid=? order by cid");
			while (rs.next()) {
				String sid      = rs.getString(1);
				String sname    = rs.getString(2).trim();
				String district = rs.getString(3);
				jsonStreet = new JSONObject();
				jsonStreet.put("sid", sid);
				jsonStreet.put("sname", sname);
				jsonStreet.put("district", district);
				jsonCommunitys = new JSONArray();
				ps.setString(1, sid);
				rs1 = ps.executeQuery();
				while (rs1.next()) {
					String cid = rs1.getString(1);
					String cname = rs1.getString(2).trim();
					j = new JSONObject();
					j.put("cid", cid);
					j.put("cname", cname);
					jsonCommunitys.put(j);
				}
				jsonStreet.put("communitys", jsonCommunitys);
				jsonAllStreet.put(jsonStreet);
			}
			response.getWriter().print(jsonAllStreet);
	  		response.getWriter().close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} catch(IOException e1) {
			logger.error(e1.getMessage(), e1);
		} catch(JSONException e2) {
			logger.error(e2.getMessage(), e2);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (rs1 != null) { try { rs1.close();	} catch (SQLException e) { ; }
				rs1 = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}
	
	private void getCommunitiesByStreet(HttpServletRequest request, HttpServletResponse response) {
		Connection conn = null;
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		JSONArray matrix = new JSONArray();
		JSONObject grid = new JSONObject();
		int total, offset;
		String page = request.getParameter("page");
		String rows = request.getParameter("rows");
		String sid = request.getParameter("sid");
		int p = Integer.parseInt(page);
		int r = Integer.parseInt(rows);
		
		try {
			offset = p * r - r;
			conn = openConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select count(*) from community where sid='" + sid + "'");
			rs.next();
			total = rs.getInt(1);
			ps = conn.prepareStatement("select * from community where sid=? order by cid offset " + 
				offset + " rows fetch next ? rows only");
			ps.setString(1, sid);
			ps.setInt(2, r);
			rs = ps.executeQuery();
			
			while (rs.next()) {
				JSONObject so = new JSONObject();
				so.put("id", rs.getString("cid"));
				String[] ss = new String[4];
				ss[0] = rs.getString("cid");
				ss[1] = rs.getString("cname").trim();
				ss[2] = rs.getString("tel").trim();
				ss[3] = rs.getString("address").trim();
				so.put("cell", ss);
				matrix.put(so);
			}
			grid.put("page", page);
			grid.put("total",(total / r) + 1);
			grid.put("records", total);
			grid.put("rows", matrix);
			response.getWriter().print(grid);
	  		response.getWriter().close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} catch(Exception e1) {
			logger.error(e1.getMessage(), e1);
			responseMessage(-1, e1.getMessage(), response);
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
				stmt = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}

	private void buildStreetSelect(HttpServletRequest request, HttpServletResponse response) {
		String district = request.getParameter("district");
		if (district == null) district = (String)request.getSession().getAttribute("udistrict");
		StringBuffer sb = new StringBuffer("<select>");
		sb.append("<option value='0'>").append(bundleMsg.getProperty("JU_USER")).append("</option>");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			conn = openConnection();
			ps = conn.prepareStatement("select sid,sname from street where district=?");
			ps.setString(1, district);
			logger.debug("district=" + district);
			rs = ps.executeQuery();
			while (rs.next())
				sb.append("<option value='").append(rs.getString(1)).append("'>").append(rs.getString(2).trim()).append("</option>");
			sb.append("</select>");
			response.setContentType("text/plain;charset=UTF-8");
	  		response.getWriter().print(sb.toString());
	  		response.getWriter().close();
		} catch(SQLException e) {
			logger.error(e.getMessage(), e);
			responseMessage(e.getErrorCode(), e.getMessage(), response);
		} catch(IOException e1) {
			logger.error(e1.getMessage());
		} finally {
			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
				rs = null;
			}
			if (ps != null) { try { ps.close();	} catch (SQLException e) { ; }
				ps = null;
			}
			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
			  	conn = null;
			}
		}
	}
	
	public static Properties getAllStreet() {
		return allStreet_;
	}
	
	public static Properties getAllCommunity() {
		return allCommunity_;
	}
	
//	private void loadAllStreetCommunity(HttpServletRequest request, HttpServletResponse response) {
//		Connection conn = null;
//		Statement stmt = null;
//		ResultSet rs = null;
//		JSONObject so = new JSONObject();
//		
//		try {
//			conn = openConnection();
//			stmt = conn.createStatement(); 
//			rs = stmt.executeQuery("select sid,sname from street");
//			while (rs.next())
//				so.put(rs.getString(1), rs.getString(2));
//			rs.close();
//			rs = stmt.executeQuery("select cid,cname from community");
//			while (rs.next())
//				so.put(rs.getString(1), rs.getString(2));
//
//			response.setContentType("text/json;charset=UTF-8");
//	  		response.getWriter().print(so);
//	  		response.getWriter().close();
//		} catch(SQLException e) {
//			logger.error(e.getMessage(), e);
//			responseMessage(e.getErrorCode(), e.getMessage(), response);
//		} catch(Exception e1) {
//			logger.error(e1.getMessage());
//		} finally {
//			if (rs != null) { try { rs.close();	} catch (SQLException e) { ; }
//				rs = null;
//			}
//			if (stmt != null) { try { stmt.close();	} catch (SQLException e) { ; }
//				stmt = null;
//			}
//			if (conn != null) { try { conn.close();	} catch (SQLException e) { ; }
//			  	conn = null;
//			}
//		}
//	}

  public void init() throws ServletException {
  	try {
  		bundleMsg.load(BaseServlet.class.getResourceAsStream("Msg.properties"));
  		allStreet_ = DBUtil.getAllStreet();
  		allCommunity_ = DBUtil.getAllCommunity();
  	} catch (IOException e) {
  		throw new ServletException(e.getMessage());
  	}
  }

  /*
  ** Utils
  */
  private boolean isEmpty(final String str) {
  	if (str == null)
  		return true;
  	else if (str.trim().length() == 0)
  	  return true;
  	else
  	  return false;
  }

  private void responseMessage(int code, String message, HttpServletResponse response) {
    JSONObject so = new JSONObject();
	try {
		response.setContentType("text/json;charset=UTF-8");
		so.put("code", code);
		so.put("message", message);
		response.getWriter().print(so.toString());
		response.getWriter().close();
	} catch(Exception e) {
		logger.error(this, e);
  	}
  }

  public static void createDB() throws SQLException{
	Connection conn = null;
	Statement stmt = null;

	conn = DriverManager.getConnection(DBURL + ";create=true");
	stmt = conn.createStatement();
	stmt.addBatch("create table street (sid char(3) primary key,sname char(10) not null,district char(1) not null,address varchar(40),tel char(11),postcode char(6))");
	stmt.addBatch("create table community (cid char(5) primary key,cname char(10) not null,district char(1) not null,sid char(3) not null constraint community_fk references street,tel char(11),address varchar(40))");
	stmt.executeBatch();
	stmt.close();
	conn.close();
  }

  public static Connection openConnection() throws SQLException {
	Connection conn = null;
	conn = DriverManager.getConnection(DBURL);
	return conn;
  }

  private void debugParameters(HttpServletRequest request) {
	  	if (!logger.isDebugEnabled())
	  		return;

	  	StringBuffer sb = new StringBuffer("Parameters -  ");
	  	java.util.Enumeration keys = request.getParameterNames();
	  	while (keys.hasMoreElements()) {
	  		String para = (String)keys.nextElement();
	  		String value = request.getParameter(para);
	  		sb.append(para).append('=').append(value).append(',');
	  	}
	  	logger.debug(sb.toString());
	}
}